package com.springboot.erp.mapper;

import com.springboot.erp.entity.Entry_Warehouse;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Component;

import java.sql.Date;
import java.sql.Timestamp;
import java.util.List;

@Mapper
@Component
public interface Entry_WarehouseMapper {
    //获取表entry_warehouse中所有信息
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,status,warehouse_no,remarks from entry_warehouse")
    List<Entry_Warehouse> get_All_Entry_Warehouse();

    //获取entry_warehouse表中一共有多少条记录
    @Select("select count(*) from entry_warehouse")
    int getRecordCount();

    //分页显示所有仓库信息
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,status,warehouse_no,remarks from entry_warehouse limit #{start},#{count}")
    List<Entry_Warehouse> get_All_Entry_Warehouse_ByPage(int start,int count);

    //删除选中的数据(根据flow_number
    @Select("delete from entry_warehouse where flow_number = #{flow_number}")
    void delete_entry_warehouse_by_flow_number(String flow_number);

    /**
     * 根据条件查询entry_warehouse
     */
    //查询(模糊查询）

    //根据warehouse_no查询
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,status,warehouse_no,remarks from entry_warehouse where warehouse_no = #{warehouse_no}")
    List<Entry_Warehouse> queryEntry_Warehouse_By_warehouse_no(String warehouse_no);
    // 根据flow_number
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,status,warehouse_no,remarks from entry_warehouse where flow_number = #{flow_number}")
    Entry_Warehouse queryEntry_Warehouse_By_flow_number(String flow_number);

    //根据list_no
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,status,warehouse_no,remarks from entry_warehouse " +
            "where list_no = #{list_no}")
    List<Entry_Warehouse> queryEntry_Warehouse_By_list_no(String list_no);

    //根据goods_name
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,status,warehouse_no,remarks from entry_warehouse " +
            "where goods_name = #{goods_name}")
    List<Entry_Warehouse> queryEntry_Warehouse_By_goods_name(String goods_name);

    //根据entry_date范围查询
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,status,warehouse_no,remarks from entry_warehouse " +
            "where entry_date between #{start_date} and #{end_date}" )
    List<Entry_Warehouse> queryEntry_Warehouse_By_entry_date(Timestamp start_date, Timestamp end_date);

    //根据list_no和goods_name查询
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,status,warehouse_no,remarks from entry_warehouse " +
            "where list_no = #{list_no} and goods_name = #{goods_name}")
    List<Entry_Warehouse> queryEntry_Warehouse_By_list_no_And_goods_name(String list_no,String goods_name);

    //根据list_no和goods_name以及entry_date查询
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,status,warehouse_no,remarks from entry_warehouse " +
            "where list_no = #{list_no} and goods_name = #{goods_name} and entry_date between #{start_date} and #{end_date}" )
    List<Entry_Warehouse> queryEntry_Warehouse_By_list_no_And_goods_name_And_entry_date(String list_no,String goods_name,Timestamp start_date,Timestamp end_date);

    //根据list_no和entry_date查询查询
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,status,warehouse_no,remarks from entry_warehouse " +
            "where list_no = #{list_no} and entry_date between #{start_date} and #{end_date}" )
    List<Entry_Warehouse> queryEntry_Warehouse_By_list_no_And_entry_date(String list_no,Timestamp start_date,Timestamp end_date);

    //根据status查询
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,status,warehouse_no,remarks from entry_warehouse " +
            "where status = #{status}" )
    List<Entry_Warehouse> queryEntry_Warehouse_By_status(String status);

    //修改选中的entry_warehouse信息
    @Update("update entry_warehouse set list_no=#{list_no}, goods_no=#{goods_no},kind=#{kind}, specification=#{specification}," +
            "goods_name=#{goods_name},single_price=#{single_price},count=#{count},total_price=#{total_price},manufacturer=#{manufacturer},entry_date=#{entry_date},status=#{status},warehouse_no=#{warehouse_no},remarks=#{remarks} where flow_number=#{flow_number}")
    void updateEntry_Warehouse(String flow_number,String list_no, String goods_no, String kind, String specification, String goods_name, double single_price,int count,double total_price,String manufacturer, Timestamp entry_date, String status, String warehouse_no,String remarks);


    //添加entry_warehouse
    @Insert("insert into entry_warehouse (flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date," +
            "status,warehouse_no,remarks) values (#{flow_number},#{list_no},#{goods_no},#{kind},#{specification},#{goods_name},#{single_price},#{count},#{total_price},#{manufacturer}," +
            "#{entry_date},#{status},#{warehouse_no},#{remarks})")
    void addEntry_Warehouse(Entry_Warehouse entry_warehouse);

    //获取序列号(入库流水号）
    @Select("select nextval('seq_entry_warehouse_flow_number')")
    String get_seq_entry_warehouse_flow_number();

    //统计某个已入库的goods_no的物品数量
    @Select("select count(*) from entry_warehouse where goods_no=#{goods_no} and status='已入库'")
    int get_goods_no_Count_entry();

    //显示每个商品编号的商品的入库数量
    @Select("select warehouse_no,goods_no,kind,specification,goods_name,single_price,count(goods_no),single_price*count(goods_no),remarks from entry_warehouse where status = '已入库' group by goods_no")
    List<Entry_Warehouse> get_count_entry_house_groupby_goods_no();

    //显示指定商品编号
    @Select("select count(*) from entry_warehouse where goods_no = #{goods_no} and status='已入库'")
    int get_entry_goods_no_Record(String goods_no);

    //找到指定goods_no的所有入库信息
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,status,warehouse_no,remarks from entry_warehouse where goods_no = #{goods_no} and status = '已入库'")
    List<Entry_Warehouse> get_by_goods_no(String goods_no);


    /*//根据flow_number查询相应的信息
    @Select("select flow_number,list_no,goods_no,kind,specification,goods_name,manufacturer,entry_date,status,warehouse_no from entry_warehouse where flow_number = #{flow_number}")
    Entry_Warehouse get_entry_warehouse_By_flow_number(String flow_number);*/

}
